4.6 Setting up the database

This section contains information about setting up your database.

4.6.1 Database versions

SIU references: SIU-009, SIU-010, SIU-279.

MyID has been tested with the following SQL Server versions:

• SQL Server 2019 – CU15 (15.0.4198.2 – January 2022)

• SQL Server 2017 – CU27 (14.0.3421.10 – October 2021)

• SQL Server 2016 SP3 (13.0.6300.2 – September 2021)

Note: Intercede supports the database versions listed above. If you are going to use different service packs or cumulative updates for these major versions than those listed above, make sure that you carry out additional testing within your environment. For production deployment, SQL Server Enterprise or Standard editions must be used. Do not use major versions that are not listed above (for example, SQL Server 2012) as these are not supported.

If you have multiple MyID application servers, you must have a Client Access License for SQL Server for each MyID application server.

MyID has also been tested using Microsoft Azure as the database. See the Microsoft Azure Integration Guide for details.

4.6.2 Database configuration considerations

SIU references: SIU-097, SIU-098, SIU-099, SIU-126.

If you are creating the MyID database using the installation program, and have selected the Windows authentication option, make sure the account you use to install the software has the correct permissions to create a database on your SQL Server.

If you are using SQL Server authentication (SQL Azure environments only), make sure the accounts you specify for the main MyID database and the authentication database have the appropriate permissions on your SQL Server, and that you have created your databases before installing MyID; see the Prerequisites section in the Microsoft Azure Integration Guide.

If you are installing MyID into an already-created database (for example, when upgrading an existing system, or installing a new system where your DBA has already created an empty database), you do not need user permissions to create a database; however, you do need permissions to alter the schema. This means that you can remove the dbcreator permission from the installation user, as long as you make sure that the user has database-level db_owner permission instead. See section 6.1.1, Installation account for more information about permissions and default schema settings for existing databases.

Make sure your SQL Server is using English (United States) as the language. MyID supports only English (United States) for the connection to SQL Server. You can view the language used in SQL Server Management Studio – right-click the database, then select Properties from the pop-up menu.

See your Microsoft SQL Server documentation for further details.

• IKB-295 – Database failures may occur when SQL Server user accounts do not use US English as the default language

  A problem has been identified that causes failure to log on to MyID with the startup user account after first installation of, and subsequent problems to occur with the installation on dates where the day and month components cannot be reversed (for example, day/month 13/12).

  This has been seen to occur when using Windows Server 2019 and SQL Server user accounts created by the MyID installer that have been created with a default language of British English (date format dmy).

  To check the user account setting in SQL Server, run the following SQL query when logged in as the MyID Application user:

  DBCC USEROPTIONS

  See the Microsoft documentation for details:

  docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-useroptions-transact-sql?view=sql-server-ver15

  The problem will occur when the dateformat value returned by this query is not mdy.

  Symptoms may include:

  • Failure to logon after first installation.
  • Failure to carry out certificate operations.

  In these cases, the following entries may be recorded in the LogEvents table of the MyID database:

  • DAL std::exception catch handler Function : Update, catch hander. Error : SQL Error: 01000
  • An error occurred inside CBOL_AuthenticationWeb::LogonEx Error: 0x8004600c IDispatch error #24076 An error occurred inside CCommandContext::SetComplete Error: 0x8004600c IDispatch error #24076 AuditCollection - error Commiting Audit Rows In object BOLContext.AuditCollection.1 In object BOLContext.CommandContext.1

  To correct the problem, modify the default language of the MyID COM+ account in SQL Server:

  1. Open SQL Server Management Studio as an administrative user

  2. Open Security > Logins.

     Note: Open the Security folder at the top level for the server, not the folder under the MyID database.

  3. Right-click, then from the pop-up menu select New Login.

     Alternatively, if the login for the MyID COM+ user already exists, double-click the login.

  4. Make sure Windows authentication is selected, then click Search to select the MyID COM+ user account.
  5. From the Default language drop-down list, select us_english.
  6. Click OK.

  If you continue to experience problems after correcting this issue, contact Intercede customer support quoting reference IKB-295.

4.6.3 Installing the database software

SIU references: SIU-092, SIU-093, SIU-096, SIU-296.

To install the database software:

1. Install the following SQL Server packages on the MyID database server:

   • Database Engine Services.

     Note: You must install the SQL Server Full Text Search option. To confirm whether Full Text Search is installed, you can run the following query:

     SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

     If this query returns 1, Full Text Search is installed. If this returns 0, you must add the feature before attempting to install MyID.

     Note: Under some circumstances, for example when setting up mirroring, the Full Text Search may stop indexing. See your Microsoft documentation for information on re-indexing the database.

   • Client Tools Connectivity.

2. On the MyID application server, install the following:

   • Microsoft OLE DB Driver 18 for SQL Server (MSOLEDBSQL).

     This driver is available from Microsoft.

     The current release has been tested with driver version 18.6.3. If you want to use a different version of the driver, contact customer support quoting reference SUP-324.

   • SQL Server Native Client 11

     This is available in the SQL Server Feature Pack.

   You must also make sure that the OLE DB Driver and Native Client are installed on the PC on which you run the database component of the installation program. For simplicity, you can run the database component of the installation program from the MyID application server.

   If you are using the standalone authentication service (web.oauth2.ext) you must make sure that the OLE DB Driver and Native Client are installed on the server onto which you want to install this service.

• IKB-361 – Problem using Microsoft OLE DB Driver 19 for SQL Server

  You cannot use the Microsoft OLE DB Driver version 19 or later as it contains a breaking change. You can obtain the required version of the driver from:

  docs.microsoft.com/en-gb/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver16#previous-releases

Note: Install only one instance of the MyID database. You can choose to install the database from the database server or the application server, but do not run the installation from both.